/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.clarice.homechef.dao;

import com.clarice.homechef.domain.Chef;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

/**
 *
 * @author Akshay Velhal
 */
@Repository
public class ChefDaoImpl implements ChefDao {
    
    @Autowired
    DataSource dataSource;
    
    private static String sqlGetChef = "select chefid, name, email, area, address, mobile from chef where email = ?";
    public Chef getChefByEmail(String username) throws Exception {
        Chef chef = new Chef();
        try (Connection conn = dataSource.getConnection();
                PreparedStatement ps = conn.prepareStatement(sqlGetChef);) {
            
            ps.setString(1, username);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
               chef.setChefId(rs.getInt("chefid"));
               chef.setName(rs.getString("name"));
               chef.setEmail(rs.getString("email"));
               chef.setArea(rs.getString("area"));
               chef.setAddress(rs.getString("address"));
               chef.setMobile(rs.getString("mobile"));
            }
        } catch(Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return chef;
    }
    
    private static String sqlGetChefById = "select chefid, name, email, area, address, mobile from chef where chefid = ?";
    public Chef getChefById(int chefId) throws Exception {
        Chef chef = new Chef();
        try (Connection conn = dataSource.getConnection();
                PreparedStatement ps = conn.prepareStatement(sqlGetChefById);) {
            
            ps.setInt(1, chefId);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
               chef.setChefId(rs.getInt("chefid"));
               chef.setName(rs.getString("name"));
               chef.setEmail(rs.getString("email"));
               chef.setArea(rs.getString("area"));
               chef.setAddress(rs.getString("address"));
               chef.setMobile(rs.getString("mobile"));
            }
        } catch(Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return chef;
    }
    
    
    
    
    
    private static String sqlGetChefRatings = "select avg(rating) as ratings, count(1) as noofreviews "
                                    +" from mealreview "
                                    +" join meal using (mealid) "
                                    +" where chefid = ?";
    public Chef getChefRatings(int chefId) throws Exception {
        Chef chef = new Chef();
        try (Connection conn = dataSource.getConnection();
                PreparedStatement ps = conn.prepareStatement(sqlGetChefRatings);) {
            ps.setInt(1, chefId);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                chef.setRating(rs.getDouble("ratings"));
                chef.setNoofreviews(rs.getInt("noofreviews"));
            }
        } catch(Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return chef;
    }
}
